/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package phd.dupenois.changeidentifiers.application.graphical.testing;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Set;
import phd.dupenois.changeidentifiers.ChangeIdentifierSet;
import phd.dupenois.changeidentifiers.ChangeIdentifierSetSnapshot;
import phd.dupenois.changeidentifiers.ChangeIdentifierSetSnapshot.ChangeIdentifierSnapshot;
import phd.dupenois.changeidentifiers.application.graphical.database.DataSourceNotInitialisedException;
import phd.dupenois.changeidentifiers.application.graphical.database.DatabaseConnection;
import phd.dupenois.changeidentifiers.application.graphical.database.DatabaseConnection.ScriptResult;
import phd.dupenois.changeidentifiers.dotpatterns.Dot;
import phd.dupenois.changeidentifiers.dotpatterns.DotPattern;
import phd.dupenois.changeidentifiers.engine.testing.Test;
import phd.dupenois.changeidentifiers.engine.testing.TestManager;
import phd.dupenois.changeidentifiers.engine.testing.TestingInitialisationException;
import phd.dupenois.changeidentifiers.engine.testing.TestingRecordException;
import phd.dupenois.changeidentifiers.engine.testing.TimeStep;
import phd.dupenois.changeidentifiers.footprints.Footprint;
import phd.dupenois.changeidentifiers.shapedifferences.SymmetricAreaDifference;
import uk.co.dupenois.geometry.Line;

/**
 *
 * @author mpd209
 */
public class DatabaseTestManager extends TestManager {

    private boolean initialised;
    public final static String TABLE_PREFIX = "ci-";
    public final static String TESTS_TABLE = TABLE_PREFIX + "tests";
    public final static String CHANGE_IDENTIFIER_SET_TABLE = TABLE_PREFIX + "change-identifier-sets";
    public final static String CHANGE_IDENTIFIER_SET_TESTS_TABLE = TABLE_PREFIX + "change-identifier-sets_tests";
    public final static String TIME_STEP_TABLE = TABLE_PREFIX + "time-steps";
    public final static String FOOTPRINT_TABLE = TABLE_PREFIX + "footprints";
    public final static String DOT_PATTERN_TABLE = TABLE_PREFIX + "dot-patterns";
    public final static String CHANGE_IDENTIFIER_SET_SNAPSHOT = TABLE_PREFIX + "change-identifier-set-snapshot";
    public final static String CHANGE_IDENTIFIER_SNAPSHOT = TABLE_PREFIX + "change-identifier-snapshot";
    private String host;
    private String db;
    private String username;
    private String password;

    /**
     * Creates a new instance of <code>DatabaseTestManager</code> without detail message.
     * @param host
     * @param db
     * @param username
     * @param password
     */
    public DatabaseTestManager(String host, String db, String username, String password) {
        this.host = host;
        this.db = db;
        this.username = username;
        this.password = password;
    }

    public void initialise()
            throws TestingInitialisationException {
        Connection connection = null;
        try {
            DatabaseConnection.initialise(host, db, username, password);
            initialised = true;
            connection = DatabaseConnection.getConnection();
            boolean databaseIsSetup = false;
            databaseIsSetup = DatabaseConnection.tableExists(connection, TESTS_TABLE);
            if (databaseIsSetup) {
                databaseIsSetup = DatabaseConnection.tableExists(connection, CHANGE_IDENTIFIER_SET_TABLE);
            }
            if (databaseIsSetup) {
                databaseIsSetup = DatabaseConnection.tableExists(connection, CHANGE_IDENTIFIER_SET_TESTS_TABLE);
            }
            if (databaseIsSetup) {
                databaseIsSetup = DatabaseConnection.tableExists(connection, TIME_STEP_TABLE);
            }
            if (databaseIsSetup) {
                databaseIsSetup = DatabaseConnection.tableExists(connection, FOOTPRINT_TABLE);
            }
            if (databaseIsSetup) {
                databaseIsSetup = DatabaseConnection.tableExists(connection, DOT_PATTERN_TABLE);
            }
            if (databaseIsSetup) {
                databaseIsSetup = DatabaseConnection.tableExists(connection, CHANGE_IDENTIFIER_SET_SNAPSHOT);
            }
            if (databaseIsSetup) {
                databaseIsSetup = DatabaseConnection.tableExists(connection, CHANGE_IDENTIFIER_SNAPSHOT);
            }

            if (!databaseIsSetup) {
                String scriptLocation = getClass().getResource("testingsetup.sql").getPath();
                ScriptResult result = DatabaseConnection.runScript(scriptLocation);
                if (result.getExitValue() != 0) {
                    throw new TestingInitialisationException("Failed to run setup script, error: '" + result.getError() + "'");
                }
            }
        } catch (SQLException ex) {
            throw new TestingInitialisationException(ex);
        } catch (DataSourceNotInitialisedException ex) {
            throw new TestingInitialisationException(ex);
        } catch (IOException ex) {
            throw new TestingInitialisationException(ex);
        } catch (InterruptedException ex) {
            throw new TestingInitialisationException(ex);
        }
    }

    public boolean isInitialised() {
        return initialised;
    }

    public void recordTest(Test test) throws TestingRecordException {
        try {
            int test_id = insertTest(test);
            int cset_id = insertChangeIdentifierSet(test.getChangeIdentifierSet());
            linkTestToChangeIdentifierSet(test_id, cset_id);

            TimeStep[] steps = test.getTimeSteps();
            TimeStep[] stepsWithoutCI = test.getTimeStepsWithoutChangeIdentifiers();
            TimeStep[] stepsWithDiff = new TimeStepWithFootprintDifferences[steps.length];
            TimeStep step;
            TimeStepWithFootprintDifferences stepWithDiff;
            TimeStep stepWithoutCI;
            SymmetricAreaDifference symmetricAreaDifferenceInstance = new SymmetricAreaDifference();
            double symmetricAreaDiff;
            for(int i=0;i<steps.length;i++){
                step = steps[i];
                stepWithDiff = TimeStepWithFootprintDifferences.getTimeStep(step);
                stepWithoutCI = stepsWithoutCI[i];
                symmetricAreaDiff = symmetricAreaDifferenceInstance.getDifference(
                        stepWithoutCI.getFootprint(), stepWithDiff.getFootprint());
                stepWithDiff.setSymmetricAreaDifference(symmetricAreaDiff);
                stepsWithDiff[i] = stepWithDiff;
            }


            insertTimeSteps(test_id, stepsWithDiff, true);
            insertTimeSteps(test_id, stepsWithoutCI, false);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new TestingRecordException(ex);
        } catch (DataSourceNotInitialisedException ex) {
            throw new TestingRecordException(ex);
        }
    }

    public void insertTimeSteps(int test_id, TimeStep[] timesteps, boolean hasChangeIdentifiers) throws SQLException, DataSourceNotInitialisedException {
        Connection connection = null;
        PreparedStatement statement = null;
        HashMap<Integer, TimeStep> idToTimestep = new HashMap<Integer, TimeStep>();
        try {
            connection = DatabaseConnection.getConnection();
            statement = connection.prepareStatement("INSERT INTO `" + TIME_STEP_TABLE + "` (`test-id`, "
                    + "`start`, `time-taken`, `algorithm-start`, `algorithm-time-taken`, " +
                    "`has-change-identifiers`, `symmetric-area-difference`, `redrew`) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
            int id;
            for (TimeStep step : timesteps) {
                statement.setLong(1, test_id);
                statement.setLong(2, step.getStarted());
                statement.setLong(3, step.getTimeTaken());
                statement.setLong(4, step.getAlgStarted());
                statement.setLong(5, step.getAlgTimeTaken());
                statement.setBoolean(6, hasChangeIdentifiers);
                if(step instanceof TimeStepWithFootprintDifferences){
                    statement.setDouble(7, ((TimeStepWithFootprintDifferences)step).getSymmetricAreaDifference());
                }else{
                    statement.setDouble(7, 0);
                }
                statement.setBoolean(8, step.isRedraw());
                statement.execute();
                id = DatabaseConnection.getLastInsertIdInt(connection);
                idToTimestep.put(id, step);
            }
        } finally {
            DatabaseConnection.closeStatement(statement);
            DatabaseConnection.closeConnection(connection);
        }
        Set<Integer> ids = idToTimestep.keySet();
        TimeStep step;
        for (int id : ids) {
            step = idToTimestep.get(id);
            insertFootprint(id, step.getFootprint());
            insertDotPattern(id, step.getDotPattern());
            if (step.getChangeIdentifierSetSnapshot() != null) {
                insertChangeIdentifierSetSnapshot(id,  step.getChangeIdentifierSetSnapshot());
            }

        }
    }

    public void insertChangeIdentifierSetSnapshot(int step_id,
            ChangeIdentifierSetSnapshot changeIdentifierSetSnapshot) throws SQLException, DataSourceNotInitialisedException {
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            connection = DatabaseConnection.getConnection();
            statement = connection.prepareStatement("INSERT INTO `" + CHANGE_IDENTIFIER_SET_SNAPSHOT + "` (`step-id`, `failcount`, "
                    + "`start`, `time-taken`, `redraw`) VALUES (?, ?, ?, ?, ?)");
            statement.setInt(1, step_id);
            statement.setInt(2, changeIdentifierSetSnapshot.getFailcount());
            statement.setLong(3, changeIdentifierSetSnapshot.getStart());
            statement.setLong(4, changeIdentifierSetSnapshot.getTimeTaken());
            statement.setBoolean(5, changeIdentifierSetSnapshot.isRedraw());
            statement.execute();
//            DatabaseConnection.closeStatement(statement);
            int snapshot_id = DatabaseConnection.getLastInsertIdInt(connection);
            ChangeIdentifierSnapshot[] ciShots = changeIdentifierSetSnapshot.getChangeIdentifierSnapshotsArray();
            statement = connection.prepareStatement("INSERT INTO `" + CHANGE_IDENTIFIER_SNAPSHOT + "` (`set-id`, "
                    + "`change-identifier-classname`, `start`, `time-taken`, "
                    + "`value`, `caused-redraw`) VALUES (?, ?, ?, ?, ?, ?)");
            for (ChangeIdentifierSnapshot shot : ciShots) {
                statement.setInt(1, snapshot_id);
                statement.setString(2, shot.getClassname());
                statement.setLong(3, shot.getStarted());
                statement.setLong(4, shot.getTimeTaken());
                statement.setDouble(5, shot.getValue());
                statement.setBoolean(6, shot.isCausedRedraw());
                statement.execute();
            }
        } finally {
            DatabaseConnection.closeStatement(statement);
            DatabaseConnection.closeConnection(connection);
        }
    }
    private String dotPatternToString(DotPattern pattern){
        StringBuffer string = new StringBuffer();
        for (Dot dot : pattern.getDots()) {
            string.append(dot.getX());
            string.append(",");
            string.append(dot.getY());
            string.append("\n");
        }
        return string.toString();
    }
    public void insertDotPattern(int step_id, DotPattern dotPattern) throws SQLException, DataSourceNotInitialisedException {
        PreparedStatement statement = null;
        Connection connection = null;
        String str_pattern = dotPatternToString(dotPattern);
        try {
            connection = DatabaseConnection.getConnection();
            statement = connection.prepareStatement("INSERT INTO `" + DOT_PATTERN_TABLE + "` (`step-id`, "
                    + "`pattern`) "
                    + "VALUES (?, ?)");
            statement.setInt(1, step_id);
            statement.setString(2, str_pattern);
            statement.execute();
        } finally {
            DatabaseConnection.closeStatement(statement);
            DatabaseConnection.closeConnection(connection);
        }
    }
    private String footprintToString(Footprint footprint){
        StringBuffer string = new StringBuffer();            
        for (Line line : footprint.getLines()) {
            string.append(line.getStart().getX());
            string.append(",");
            string.append(line.getStart().getY());
            string.append(",");
            string.append(line.getFinish().getX());
            string.append(",");
            string.append(line.getFinish().getY());
            string.append("\n");
        }
        return string.toString();
    }
    public void insertFootprint(int step_id, Footprint footprint) throws SQLException, DataSourceNotInitialisedException {
        PreparedStatement statement = null;
        Connection connection = null;
        String str_footprint = footprintToString(footprint);
        try {
            connection = DatabaseConnection.getConnection();
            statement = connection.prepareStatement("INSERT INTO `" + FOOTPRINT_TABLE + "` (`step-id`, "
                    + "`footprint`) "
                    + "VALUES (?, ?)");
            statement.setInt(1, step_id);
            statement.setString(2, str_footprint);
            statement.execute();
        } finally {
            DatabaseConnection.closeStatement(statement);
            DatabaseConnection.closeConnection(connection);
        }
    }

    public void linkTestToChangeIdentifierSet(int test_id, int cset_id) throws SQLException, DataSourceNotInitialisedException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DatabaseConnection.getConnection();
            statement = connection.prepareStatement("INSERT INTO `" + CHANGE_IDENTIFIER_SET_TESTS_TABLE + "` (`test-id`, "
                    + "`set-id`) VALUES (?, ?)");
            statement.setInt(1, test_id);
            statement.setInt(2, cset_id);
            statement.execute();
        } finally {
            DatabaseConnection.closeStatement(statement);
            DatabaseConnection.closeConnection(connection);
        }
    }

    public int insertChangeIdentifierSet(ChangeIdentifierSet changeIdentifierSet) throws SQLException, DataSourceNotInitialisedException {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DatabaseConnection.getConnection();
            statement = connection.prepareStatement("SELECT `id` FROM `" + CHANGE_IDENTIFIER_SET_TABLE + "` WHERE "
                    + "`name` = ? AND `version` = ?");
            statement.setString(1, changeIdentifierSet.getName());
            statement.setString(2, changeIdentifierSet.getVersion());
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt("id");
            }
            DatabaseConnection.closeResultSet(resultSet);
            DatabaseConnection.closeStatement(statement);
            statement = connection.prepareStatement("INSERT INTO `" + CHANGE_IDENTIFIER_SET_TABLE + "` (`name`, "
                    + "`version`, `description`, `max-fails`, `total-threshold`) "
                    + "VALUES (?, ?, ?, ?, ?)");
            statement.setString(1, changeIdentifierSet.getName());
            statement.setString(2, changeIdentifierSet.getVersion());
            statement.setString(3, changeIdentifierSet.getDescription());
            statement.setInt(4, changeIdentifierSet.getMaxFails());
            statement.setDouble(5, changeIdentifierSet.getTotalThreshold());
            statement.execute();
            return DatabaseConnection.getLastInsertIdInt(connection);
        } finally {
            DatabaseConnection.closeStatement(statement);
            DatabaseConnection.closeConnection(connection);
        }
    }

    public int insertTest(Test test) throws SQLException, DataSourceNotInitialisedException {
        
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DatabaseConnection.getConnection();
            statement = connection.prepareStatement("INSERT INTO `" + TESTS_TABLE + "` (`title`, "
                    + "`description`, `footprint-algorithm`, `start`, `time-taken`, "
                    + "`start-without-change-identifiers`, `time-taken-without-change-identifiers`) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?)");
            statement.setString(1, test.getTitle());
            statement.setString(2, test.getDescription());
            statement.setString(3, test.getFootprintAlgorithmClass());
            statement.setLong(4, test.getStart());
            statement.setLong(5, test.getTimeTaken());
            statement.setLong(6, test.getStartWithoutChangeIdentifiers());
            statement.setLong(7, test.getTimeTakenWithoutChangeIdentifiers());
            statement.execute();
            return DatabaseConnection.getLastInsertIdInt(connection);
        } finally {
            DatabaseConnection.closeStatement(statement);
            DatabaseConnection.closeConnection(connection);
        }
    }
}
